﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    class MiscChargeController
    {
        private DBConnector connector;

        public MiscChargeController()
        {
            connector = new DBConnector();
        }

        public DataTable getData()
        {
            string query = "SELECT periodStart as 'Start Date', periodEnd as 'End Date', FORMAT(miscCharges,2) as 'Misc. Charges' FROM period ORDER BY periodStart DESC";
            if (connector.openConnection())
            {
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, connector.getConnection());
                MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                connector.closeConnection();
                return dataTable;
            }
            else
                return null;
        }

        public DataTable getPreviousData()
        {
            DateTime periodStart = DateTime.Parse(getCurrentDate());
            string query = "SELECT periodStart as 'Start Date', periodEnd as 'End Date' FROM period WHERE periodStart < \'"+periodStart.ToString("yyyy-MM-dd")+"\'";
            if (connector.openConnection())
            {
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, connector.getConnection());
                MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                connector.closeConnection();
                return dataTable;
            }
            else
                return null;
        }

        public bool editMiscCharge(MiscChargePeriod miscCharge)
        {
            int id = getPeriodID();
            string query = "UPDATE period SET miscCharges ="+miscCharge.getMiscCharge()+" WHERE periodID = "+id;
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandText = query;
                    cmd.Connection = connector.getConnection();
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message);
                    return false;
                }
            }
            else
                return false;
        }

        public int getPeriodID()
        {
            int id = -1;
            string query = "SELECT periodID from period order by periodStart desc;";
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    id = (int)dataReader["periodID"];
                dataReader.Close();
                connector.closeConnection();
            }
            return id;
        }

        public int getPeriodID(DateTime date)
        {
            int id = -1;
            String check = date.ToString("yyyy-MM-dd");
            string query = "SELECT periodID from period WHERE periodStart <= \'" + check + "\' AND periodEnd >= \'" + check + "\'";
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    id = (int)dataReader["periodID"];
                dataReader.Close();
                connector.closeConnection();
            }
            return id;
        }

        public String getCurrentDate()
        {
            string date="";
            string query = "SELECT periodStart from period order by periodStart DESC";
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    date = dataReader["periodStart"] + "";
                dataReader.Close();
                connector.closeConnection();
            }
            return date;
        }

        public String getCurrentEnd()
        {
            string date = "";
            string query = "SELECT periodEnd from period order by periodStart DESC";
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    date = dataReader["periodEnd"] + "";
                dataReader.Close();
                connector.closeConnection();
            }
            return date;
        }

        public decimal getCurrentChargeAmount()
        {
            decimal amount=0;
            string query = "SELECT miscCharges from period order by periodStart DESC";
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    amount = Decimal.Parse(dataReader["miscCharges"]+"");
                dataReader.Close();
                connector.closeConnection();
            }
            return amount;
        }

        public bool addPeriod(MiscChargePeriod period)
        {
            string query = "INSERT INTO period (periodStart,periodEnd,miscCharges) VALUES(\'" + period.getStartDate() + "\', \'" + period.getEndDate() + "\', " + period.getMiscCharge() + ");";
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    return false;
                }
            }
            else 
                return false;
        }

        public bool isNotEmpty()
        {
            string query = "SELECT miscCharges from period;";
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                bool flag = dataReader.Read();
                dataReader.Close();
                connector.closeConnection();
                return flag;
            }
            return false;
        }

        public bool updateEndDate(MiscChargePeriod miscCharge)
        {
            int id = getPeriodID();
            string query = "UPDATE period SET periodEnd = \'" + miscCharge.getEndDate() + "\' WHERE periodID = " + id;
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandText = query;
                    cmd.Connection = connector.getConnection();
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    return false;
                }
            }
            else
                return false;
        }

        public void updatePeriod()
        {
            DateTime periodPreviousEnd = DateTime.Parse(getCurrentEnd());
            DateTime periodStart = periodPreviousEnd.AddDays(1);
            string query = "INSERT INTO period(periodStart,periodEnd,miscCharges) VALUES (\'" + periodStart.ToString("yyyy-MM-dd") + "\', \'" + periodStart.ToString("yyyy-MM-dd") + "\', 0);";
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }
    }
}
